using System;
using System.Collections.Generic;
using System.Text;
using NBusiness.Data.Providers.SqlServer.Query;
using NBusiness.Query;
using System.Reflection;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace NBusiness.Query.Testing
{
    [TestClass]
    public class SelectTests
    {
        [TestMethod]
        public void SimpleSelect()
        {
            SqlTable table = new SqlTable("Test");
            SqlColumn a = new SqlColumn("id", table);
            SqlColumn b = new SqlColumn("data", table);

            IStatement select = table.Select(a, b);

            string expected = "SELECT [A].[id], [A].[data] FROM [Test] AS [A]";
            string actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectWithJoin()
        {
            SqlTable foo = new SqlTable("Foo", "F");
            SqlTable bar = new SqlTable("Bar", "B");

            SqlColumn c1 = new SqlColumn("c1", foo);
            SqlColumn c2 = new SqlColumn("c2", foo);
            SqlColumn c3 = new SqlColumn("c3", bar);
            SqlColumn c4 = new SqlColumn("c4", bar);

            IStatement select = foo.Select().From(bar.InnerJoin(c2 == c3)).Select(c1, c4);

            string expected = "SELECT [F].[c1], [B].[c4] FROM [Foo] AS [F] INNER JOIN [Bar] AS [B] ON ([F].[c2]=[B].[c3])";
            string actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectBooleanOperators()
        {
            SqlTable foo = new SqlTable("Foo");

            SqlColumn c = new SqlColumn("c", foo);
            SqlParameter p = new SqlParameter("p");

            IStatement select = foo.Where(c == p);

            string expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c]=@p)";
            string actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c != p);

            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c]<>@p)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c < p);
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c]<@p)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c <= p);
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c]<=@p)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c > p);
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c]>@p)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c >= p);
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c]>=@p)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c.Like(p));
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c] LIKE @p)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c.NotLike(p));
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c] NOT LIKE @p)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c.Is(new SqlPrimitive("NULL")));
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c] IS NULL)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where(c.IsNot(new SqlPrimitive("NULL")));
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c] IS NOT NULL)";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            ITable bar = new SqlTable("bar");
            ISelect sub = bar.Select();
            select = foo.Where(c.In(sub));
            expected = "SELECT * FROM [Foo] AS [A] WHERE ([A].[c] IN (SELECT * FROM [bar] AS [C]) AS [B])";
            actual = select.ToSql();
            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectAndTests()
        {
            SqlTable foo = new SqlTable("Foo");
            
            SqlColumn c1 = new SqlColumn("c1", foo);
            SqlColumn c2 = new SqlColumn("c2", foo);

            IStatement select = foo.Where((c1 == "@p1") & (c2 == "@p2"));

            string expected = "SELECT * FROM [Foo] AS [A] WHERE (([A].[c1]=@p1) AND ([A].[c2]=@p2))";
            string actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where((c1 == "@p1") & ((c2 == "@p2") & (c1 == c2)));

            expected = "SELECT * FROM [Foo] AS [A] WHERE (([A].[c1]=@p1) AND (([A].[c2]=@p2) AND ([A].[c1]=[A].[c2])))";
            actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectOrTests()
        {
            SqlTable foo = new SqlTable("Foo");

            SqlColumn c1 = new SqlColumn("c1", foo);
            SqlColumn c2 = new SqlColumn("c2", foo);

            IStatement select = foo.Where((c1 == "@p1") | (c2 == "@p2"));

            string expected = "SELECT * FROM [Foo] AS [A] WHERE (([A].[c1]=@p1) OR ([A].[c2]=@p2))";
            string actual = select.ToSql();
            Assert.AreEqual(expected, actual);

            select = foo.Where((c1 == "@p1") | ((c2 == "@p2") | (c1 == c2)));

            expected = "SELECT * FROM [Foo] AS [A] WHERE (([A].[c1]=@p1) OR (([A].[c2]=@p2) OR ([A].[c1]=[A].[c2])))";
            actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectWithOrderBy()
        {
            SqlTable table = new SqlTable("Test", "T");
            SqlColumn a = new SqlColumn("A", "a", table);
            SqlColumn b = new SqlColumn("B", "b", table);

            IStatement select = table.Select(a, b).OrderBy(SortOrder.Asc, a, b);
            
            string expected = "SELECT [T].[A] AS [a], [T].[B] AS [b] FROM [Test] AS [T] ORDER BY [T].[A], [T].[B] ASC";
            string actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectWithGroupBy()
        {
            SqlTable table = new SqlTable("Test", "T");
            SqlColumn a = new SqlColumn("A", "a", table);
            SqlColumn b = new SqlColumn("B", "b", table);

            IStatement select = table.GroupBy(a).Select(a, b);

            string expected = "SELECT [T].[A] AS [a], [T].[B] AS [b] FROM [Test] AS [T] GROUP BY [T].[a]";
            string actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectWithMultipleGroupBy()
        {
            SqlTable table = new SqlTable("Test", "T");
            SqlColumn a = new SqlColumn("A", "a", table);
            SqlColumn b = new SqlColumn("B", "b", table);

            IStatement select = table.GroupBy(a, b).Select(a, b);

            string expected = "SELECT [T].[A] AS [a], [T].[B] AS [b] FROM [Test] AS [T] GROUP BY [T].[a], [T].[b]";
            string actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectWithMultipleGroupByNoAlias()
        {
            SqlTable table = new SqlTable("Test");
            SqlColumn a = new SqlColumn("A", "a", table);
            SqlColumn b = new SqlColumn("B", "b", table);

            IStatement select = table.GroupBy(a, b).Select(a, b);

            string expected = "SELECT [Test].[A] AS [a], [Test].[B] AS [b] FROM [Test] GROUP BY [Test].[a], [Test].[b]";
            string actual = select.ToSql(new SqlQueryContext(AliasAssignmentStyle.TableName));

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void SelectWithIn()
        {
            SqlTable foo = new SqlTable("Foo", "f");
            SqlColumn a = new SqlColumn("A", foo);
            SqlColumn b = new SqlColumn("B", foo);

            SqlTable bar = new SqlTable("Bar", "b");
            SqlColumn c = new SqlColumn("C", bar);

            ISelect subQuery = bar.Select(c).As("c");

            ISelect select = foo.Select(a, b).Where(a.In(subQuery));

            string expected = "SELECT [f].[A], [f].[B] FROM [Foo] AS [f] WHERE ([f].[A] IN (SELECT [b].[C] FROM [Bar] AS [b]) AS [c])";
            string actual = select.ToSql();

            Assert.AreEqual(expected, actual);

            select = foo.Select(a, b).Where(a.NotIn(subQuery));
            expected = "SELECT [f].[A], [f].[B] FROM [Foo] AS [f] WHERE ([f].[A] NOT IN (SELECT [b].[C] FROM [Bar] AS [b]) AS [c])";
            actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void TableHelper()
        {
            SqlTable foo = new SqlTable("Foo", "f");
            SqlColumn c1 = new SqlColumn("c1", foo);
            IStatement select = foo.Where(c1 == "test");

            string expected = "SELECT * FROM [Foo] AS [f] WHERE ([f].[c1]='test')";
            string actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void InnerJoinOnSubQuery()
        {
            ITable foo = new SqlTable("Foo", "f");
            IColumn a = new SqlColumn("A", "a", foo);

            ITable gah = new SqlTable("Gah", "g");
            IColumn b = new SqlColumn("B", "b", gah);
            IColumn c = new SqlColumn("C", "c", gah);

            ISelect subQuery = gah.Select(b, c).As("gah");
            IColumn subB = new SqlColumn("b", subQuery);
            IColumn subC = new SqlColumn("c", subQuery);

            ISelect select = foo.Select(a, subC).From(subQuery.InnerJoin(a.Equal(subB)));

            string expected = "SELECT [f].[A] AS [a], [gah].[c] FROM [Foo] AS [f] INNER JOIN (SELECT [g].[B] AS [b], [g].[C] AS [c] FROM [Gah] AS [g]) AS [gah] ON ([f].[A]=[gah].[b])";
            string actual = select.ToSql();

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void OutterLeftJoinTest()
        {
            ITable f = new SqlTable("Foo", "f");
            IColumn a = new SqlColumn("A", "a", f);

            ITable g = new SqlTable("Gah", "g");
            IColumn b = new SqlColumn("B", "b", g);

            IJoin join = g.OutterLeftJoin(b.Equal(a)) as IJoin;

            string expected = "OUTTER LEFT JOIN [Gah] AS [g] ON ([g].[B]=[f].[A])";
            string actual = join.ToSql();

            Assert.AreEqual(expected, actual);
            Assert.AreEqual(g, join.Source);
            Assert.AreEqual(b, join.On.Left);
            Assert.AreEqual(a, join.On.Right);
        }

        [TestMethod]
        public void OutterRightJoinTest()
        {
            ITable f = new SqlTable("Foo", "f");
            IColumn a = new SqlColumn("A", "a", f);

            ITable g = new SqlTable("Gah", "g");
            IColumn b = new SqlColumn("B", "b", g);

            IJoin join = g.OutterRightJoin(b.Equal(a)) as IJoin;

            string expected = "OUTTER RIGHT JOIN [Gah] AS [g] ON ([g].[B]=[f].[A])";
            string actual = join.ToSql();

            Assert.AreEqual(expected, actual);
            Assert.AreEqual(g, join.Source);
            Assert.AreEqual(b, join.On.Left);
            Assert.AreEqual(a, join.On.Right);
        }

        [TestMethod]
        public void BooleanTest()
        {
            SqlEqual equal = new SqlEqual();
            Assert.AreEqual(false, equal.Not);

            equal.Not = true;
            Assert.AreEqual(true, equal.Not);

            SqlGreaterThan gt = new SqlGreaterThan();
            Assert.AreEqual(false, gt.OrEqual);
            gt.OrEqual = true;
            Assert.AreEqual(true, gt.OrEqual);

            SqlIn @in = new SqlIn();
            Assert.AreEqual(false, @in.Not);
            @in.Not = true;
            Assert.AreEqual(true, @in.Not);

            SqlIs @is = new SqlIs();
            Assert.AreEqual(false, @is.Not);
            @is.Not = true;
            Assert.AreEqual(true, @is.Not);

            SqlLessThan lt = new SqlLessThan();
            Assert.AreEqual(false, lt.OrEqual);
            lt.OrEqual = true;
            Assert.AreEqual(true, lt.OrEqual);

            SqlLike like = new SqlLike();
            Assert.AreEqual(false, like.Not);
            like.Not = true;
            Assert.AreEqual(true, like.Not);
        }

        [TestMethod]
        public void SqlInTest()
        {
            SqlIn @in = new SqlIn();
            Assert.AreEqual(false, @in.Not);
            @in.Not = true;
            Assert.AreEqual(true, @in.Not);

            SqlPrimitive p1 = new SqlPrimitive(1);
            SqlPrimitive p2 = new SqlPrimitive(2);
            @in = new SqlIn(p1, p2);
            Assert.AreEqual(false, @in.Not);
            Assert.AreEqual(p1, @in.Left);
            Assert.AreEqual(p2, @in.Right);

            @in = new SqlIn(p2, p1, true);
            Assert.AreEqual(true, @in.Not);
            Assert.AreEqual(p2, @in.Left);
            Assert.AreEqual(p1, @in.Right);
        }

        [TestMethod]
        public void SqlIsTest()
        {
            SqlIs @is = new SqlIs();
            Assert.AreEqual(false, @is.Not);
            @is.Not = true;
            Assert.AreEqual(true, @is.Not);

            SqlPrimitive p1 = new SqlPrimitive(1);
            SqlPrimitive p2 = new SqlPrimitive(2);
            @is = new SqlIs(p1, p2);
            Assert.AreEqual(false, @is.Not);
            Assert.AreEqual(p1, @is.Left);
            Assert.AreEqual(p2, @is.Right);

            @is = new SqlIs(p2, p1, true);
            Assert.AreEqual(true, @is.Not);
            Assert.AreEqual(p2, @is.Left);
            Assert.AreEqual(p1, @is.Right);
        }

        [TestMethod]
        [ExpectedException(typeof(ArgumentNullException))]
        public void SqlColumnTest1()
        {
            SqlColumn column = new SqlColumn(null);
        }

        [TestMethod]
        public void SqlColumnTest2()
        {
            SqlColumn column = new SqlColumn("Test", "T");

            Assert.AreEqual("Test", column.Name);
            Assert.AreEqual("T", column.Alias);
            Assert.AreEqual(null, column.Parent);

            column = column.As("T2") as SqlColumn;
            Assert.AreEqual("T2", column.Alias);

            column.Name = "TEST";
            Assert.AreEqual("TEST", column.Name);
            column.Alias = "T3";
            Assert.AreEqual("T3", column.Alias);

            SqlTable table = new SqlTable("A");
            column.Parent = table;
            Assert.AreSame(table, column.Parent);

            string expected = "[B].[TEST] AS [T3]";
            string actual = column.ToSql();
            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void OrderByTest1()
        {
            IColumn c1 = new SqlColumn("a");
            IColumn c2 = new SqlColumn("b");
            IOrder order = new SqlOrderBy(c1, c2);

            Assert.AreEqual(c1, order.Columns[0]);
            Assert.AreEqual(c2, order.Columns[1]);
            Assert.AreEqual(SortOrder.Asc, order.Order);

            order.Order = SortOrder.Desc;
            Assert.AreEqual(SortOrder.Desc, order.Order);

            string expected = "ORDER BY [a], [b] DESC";

            Assert.AreEqual(expected, order.ToSql());
            Assert.AreEqual(expected, order.ToString());
        }

        [TestMethod]
        public void SqlParameterTests()
        {
            IParameter p1 = new SqlParameter();
            p1.Name = "test";

            string expected = "test";
            Assert.AreEqual(expected, p1.Name);

            expected = "@test";
            Assert.AreEqual(expected, p1.ToSql());
            Assert.AreEqual(expected, p1.ToString());

            p1.Name = "@test";
            Assert.AreEqual(expected, p1.ToSql());
        }

        [TestMethod]
        public void SqlPrimitiveTests()
        {
            SqlPrimitive p1 = new SqlPrimitive();
            p1.Value = null;
            Assert.AreEqual(null, p1.Value);

            string expected = "NULL";
            string actual = p1.ToSql();
            Assert.AreEqual(expected, actual);

            Guid g = new Guid("B859C00C-5405-47e3-B729-9284FE552915");
            p1.Value = g;
            expected = "'B859C00C-5405-47e3-B729-9284FE552915'".ToLower();
            actual = p1.ToSql();
            Assert.AreEqual(expected, actual);

            DateTime dt = new DateTime(2000, 12, 1);
            p1.Value = dt;
            expected = "'" + dt.ToString() + "'";
            actual = p1.ToSql();
            Assert.AreEqual(expected, actual);

            p1.Value = "test";
            expected = "'test'";
            actual = p1.ToSql();
            Assert.AreEqual(expected, actual);

            p1.Value = 1;
            expected = "1";
            actual = p1.ToSql();
            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        [ExpectedException(typeof(InvalidOperationException))]
        public void BuildFromFail1()
        {
            ISelect s = new SqlSelect();
            s.ToSql();
        }

        [TestMethod]
        public void OutterLeftJoin()
        {
            ITable t1 = new SqlTable("T1");
            ITable t2 = new SqlTable("T2");
            IColumn c1 = t1.Column("C1");
            IColumn c2 = t2.Column("C2");

            ISelect s = t1.Select().From(t2.Select().OutterLeftJoin(c2.Equal(c1)));

            string expected = "SELECT * FROM [T1] AS [A] OUTTER LEFT JOIN (SELECT * FROM [T2] AS [C]) AS [B] ON ([C].[C2]=[A].[C1])";
            string actual = s.ToSql();

            int dif = expected.CompareTo(actual);

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        public void OutterRightJoin()
        {
            ITable t1 = new SqlTable("T1");
            ITable t2 = new SqlTable("T2");
            IColumn c1 = t1.Column("C1");
            IColumn c2 = t2.Column("C2");

            ISelect s = t1.Select().From(t2.Select().OutterRightJoin(c2.Equal(c1)));

            string expected = "SELECT * FROM [T1] AS [A] OUTTER RIGHT JOIN (SELECT * FROM [T2] AS [C]) AS [B] ON ([C].[C2]=[A].[C1])";
            string actual = s.ToSql();

            int dif = expected.CompareTo(actual);

            Assert.AreEqual(expected, actual);
        }

        [TestMethod]
        [ExpectedException(typeof(ArgumentException))]
        public void MultipleFromNoJoinsFail()
        {
            ITable t1 = new SqlTable("t1");
            ITable t2 = new SqlTable("t2");

            t1.Select().From(t2).ToSql();
        }


        [TestMethod]
        public void TestParameters1()
        {
            ISelect s = new SqlSelect();
            IParameterValue p1 = s.Parameter("p1");
            IParameterValue p2 = s.Parameter("p2");

            Assert.AreEqual(2, s.Parameters.Count);
            Assert.AreSame(p1, s.Parameters[0]);
            Assert.AreSame(p2, s.Parameters[1]);
        }

        [TestMethod]
        public void TestUniqueParameters()
        {
            ISelect s = new SqlSelect();
            IParameterValue p1 = s.Parameter("p1");
            IParameterValue p2 = s.Parameter("p1");

            Assert.AreSame(p1, p2);
        }

        [TestMethod]
        public void TestParameterWithValue()
        {
            ISelect s = new SqlSelect();
            s.Parameter("p1", ParameterValueDirection.Output).Value = true;

            Assert.AreEqual(1, s.Parameters.Count);
            Assert.AreEqual("p1", s.Parameters[0].Name);
            Assert.AreEqual(ParameterValueDirection.Output, s.Parameters[0].Direction);
            Assert.AreEqual(true, s.Parameters[0].Value);
        }

    }
}
